

******************************************************
*Preparing Tax Namelist for merge
******************************************************

*****************************
*** Excel to DTA


set excelxlsxlargefile on
forvalues num=2008(1)2017{
	import delimit "$root/Data/Original/Grunddaten_`num'_namen.csv", varnames(1) clear
	rename stj STJ
	rename pid PID
	rename name_dt NAME_DT
	rename vname_dt VNAME_DT
	rename name_ep NAME_EP
	rename vname_ep VNAME_EP
	rename str_na STR_NA
	rename str_nr STR_NR
	rename plz PLZ
	rename ort ORT
	save "$root/Data/Original/Grunddaten_`num'.dta", replace
} 



************************************
*** Combine Years
***********************************

use "$root/Data/Original/Grunddaten_2008.dta", clear
forvalues ii=2009(1)2017 {
	append using "$root/Data/Original/Grunddaten_`ii'.dta"
}

preserve
drop NAME_DT VNAME_DT
rename VNAME_EP Vorname
rename NAME_EP Nachname
drop if Nachname=="" | Nachname=="(null)" | Nachname=="NULL"
tempfile partners
save `partners', replace
restore

drop   NAME_EP VNAME_EP
rename VNAME_DT Vorname
rename NAME_DT Nachname

append using `partners'

rename STJ year
rename STR_NA Strasse
rename STR_NR HausNr


save "$root/Data/Original/Grunddaten_namelist.dta", replace


*************************************
*** FILE FOR MERGE
***************************************
use "$root/Data/Original/Grunddaten_namelist.dta", clear
drop ORT

* Transform every year separately to reduce computation times
forvalues ii=2008(1)2017 {
	preserve
	keep if year==`ii'
	rename PID ID
	do "$root/Code/Preparation/Management/Merge_Prepare_Names_01.do"
	rename ID PID
	do "$root/Code/Preparation/Management/Merge_Prepare_Adress_01.do"
	tempfile year`ii'
	save `year`ii'',replace
	restore
}


clear
forvalues ii=2008(1)2017 {
	append using `year`ii''
}

duplicates drop year PID Name Adress Adress_withletter, force

duplicates tag year Name Adress,g(duplicates)
drop if duplicates>0
drop duplicates

save "$root/Data/Original/Grunddaten_merge.dta", replace



